/*
 Navicat Premium Dump SQL

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 80031 (8.0.31)
 Source Host           : 127.0.0.1:3306
 Source Schema         : lib_dtb

 Target Server Type    : MySQL
 Target Server Version : 80031 (8.0.31)
 File Encoding         : 65001

 Date: 16/12/2024 14:05:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '图书名字',
  `author` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '作者',
  `score` float(5,1) NOT NULL COMMENT '评分',
  `count` int unsigned NOT NULL COMMENT '图书数量',
  `ISBN` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `category` varchar(5) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='图书表';

-- ----------------------------
-- Records of book
-- ----------------------------
BEGIN;
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (1, '平凡世界', '路遥', 9.0, 10, '9875562323', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (2, '我与地坛', '史铁生', 9.4, 20, '9784613231', '散文');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (3, '黄金时代', '王小波', 8.5, 15, '9874651320', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (4, '文化苦旅', '余秋雨', 8.6, 10, '9746513646', '散文');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (5, '撒哈拉的故事', '三毛', 9.2, 18, '9784651342', '散文');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (6, '围城', '钱钟书', 8.9, 20, '9843697512', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (7, '红楼梦', '曹雪芹', 9.6, 12, '9746582256', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (8, '呐喊', '鲁迅', 9.0, 20, '9841254521', '小说集');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (9, '白鹿原', '陈忠实', 9.2, 25, '9865475123', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (10, '三国演义', '罗贯中', 9.3, 35, '9864251007', '小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (11, '百年孤独', '加西亚马尔克斯', 9.4, 28, '7586413507', '外国小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (12, '飘', '玛格丽特米切尔', 9.3, 24, '7585410014', '外国小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (13, '人类简史', '尤瓦尔赫拉利', 9.1, 19, '7521420467', '历史小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (14, '明朝那些事儿', '当年明月', 9.1, 14, '9871552663', '历史小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (15, '中国历代政治得失', '钱穆', 9.2, 43, '9846637520', '历史著作');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (16, '追风筝的人', '卡勒德胡赛尼', 8.9, 27, '7841004536', '外国小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (17, '傲慢与偏见', '奥斯丁', 8.9, 14, '7846995204', '外国小说');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (18, '野草', '鲁迅', 9.4, 11, '9846210125', '散文诗集');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (19, '史记', '司马迁', 9.5, 25, '9845276040', '纪传体史书');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (20, '人间词话', '王国维', 9.0, 32, '9742655784', '文学批判');
INSERT INTO `book` (`id`, `name`, `author`, `score`, `count`, `ISBN`, `category`) VALUES (22, '12', '12', 1.2, 12, '12', '12');
COMMIT;

-- ----------------------------
-- Table structure for borrow
-- ----------------------------
DROP TABLE IF EXISTS `borrow`;
CREATE TABLE `borrow` (
  `borrow_id` int NOT NULL AUTO_INCREMENT COMMENT '用于唯一标识每条借阅记录的序号，自动递增\r\n',
  `user_id` int NOT NULL COMMENT '关联借阅图书的用户在User表中的唯一标识，确保借阅记录与有效用户相关联\r\n',
  `book_id` int NOT NULL COMMENT '关联被借阅图书在Book表中的唯一标识，确保借阅记录对应实际存在的图书\r\n',
  `borrow_date` date NOT NULL COMMENT '记录图书被借阅的日期\r\n',
  `due_date` date NOT NULL COMMENT '规定图书应归还的截止日期\r\n',
  `return_date` date DEFAULT NULL COMMENT '用于记录图书实际归还的日期，若为空则表示图书尚未归还\r\n',
  PRIMARY KEY (`borrow_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='借阅记录表';

-- ----------------------------
-- Records of borrow
-- ----------------------------
BEGIN;
INSERT INTO `borrow` (`borrow_id`, `user_id`, `book_id`, `borrow_date`, `due_date`, `return_date`) VALUES (10, 4, 1, '2024-12-15', '2024-12-30', '2024-12-15');
INSERT INTO `borrow` (`borrow_id`, `user_id`, `book_id`, `borrow_date`, `due_date`, `return_date`) VALUES (11, 4, 1, '2024-12-11', '2024-12-13', NULL);
COMMIT;

-- ----------------------------
-- Table structure for fine
-- ----------------------------
DROP TABLE IF EXISTS `fine`;
CREATE TABLE `fine` (
  `fine_id` int NOT NULL AUTO_INCREMENT COMMENT '唯一标识每条罚款记录的序号，自动递增\r\n',
  `user_id` int NOT NULL COMMENT '关联被罚款用户在User表中的唯一标识，确保罚款记录对应存在的用户\r\n',
  `book_id` int NOT NULL COMMENT '图书id',
  `amount` decimal(5,2) NOT NULL COMMENT '记录用户需要缴纳的罚款金额，精确到小数点后两位\r\n',
  `due_date` date NOT NULL COMMENT '设定罚款需要支付的截止日期\r\n',
  `paid` tinyint(1) NOT NULL DEFAULT '0' COMMENT '表示罚款是否已支付，默认值为FALSE（未支付）\r\n',
  `borrow_id` int NOT NULL COMMENT '借阅记录id',
  PRIMARY KEY (`fine_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='罚款表';

-- ----------------------------
-- Records of fine
-- ----------------------------
BEGIN;
INSERT INTO `fine` (`fine_id`, `user_id`, `book_id`, `amount`, `due_date`, `paid`, `borrow_id`) VALUES (8, 4, 1, 30.00, '2024-12-23', 0, 11);
COMMIT;

-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `user_id` int NOT NULL COMMENT '用户id',
  `book_id` int NOT NULL COMMENT '图书id',
  `date` datetime NOT NULL COMMENT '评论时间',
  `message` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '留言评论',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='用户购买图书记录表';

-- ----------------------------
-- Records of message
-- ----------------------------
BEGIN;
INSERT INTO `message` (`id`, `user_id`, `book_id`, `date`, `message`) VALUES (4, 4, 1, '2024-12-16 00:00:00', '11');
INSERT INTO `message` (`id`, `user_id`, `book_id`, `date`, `message`) VALUES (7, 1, 1, '2024-12-16 00:00:00', 'åååå');
INSERT INTO `message` (`id`, `user_id`, `book_id`, `date`, `message`) VALUES (8, 1, 1, '2024-12-16 00:00:00', 'ä»vå¨vå¨vå¨vå');
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `username` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名，唯一',
  `password` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
  `email` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
  `type` int NOT NULL COMMENT '类型0用户1管理员\r\n',
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`username`, `password`, `email`, `type`, `user_id`) VALUES ('sentinel', 'sentinel', '1', 1, 1);
INSERT INTO `user` (`username`, `password`, `email`, `type`, `user_id`) VALUES ('1', '1', '12@qq.com', 0, 4);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
